package com.scm.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.scm.config.ScmConfig;
import com.scm.model.Poitem;
import com.scm.model.Pomain;
import com.scm.util.DbUtil;
import com.scm.util.StringUtil;

/**
 * 采购单业务
 * @author Surrik_Zhang
 */
public class PomainDao extends BaseDao<Pomain>{
	private static final Logger LOGGER = Logger.getLogger(PomainDao.class);
	
	public PomainDao(Connection conn) {
		super();
		this.conn = conn;
	}
	
	public PomainDao() {
	}
	//了结
	public int end(String poId) throws SQLException {
			PreparedStatement st = null;
			try {
				st = conn.prepareStatement("update pomain set status=5,endTime=now() where poId = ?");
				st.setLong(1, Long.parseLong(poId));
				return st.executeUpdate();
			}finally {
				DbUtil.close(conn, null, st);
			}
		}
		
		//修改
		public int change(Pomain pomain) throws SQLException {
			PreparedStatement st = null;
			try {
				st = conn.prepareStatement("update pomain set createTime=?,venderCode=?,tipFee=?,productTotal=?,payType=?,remark=?,prePayFee=?,poTotal=? where poId=? ");
				st.setString(1, pomain.getCreateTime());
				st.setString(2, pomain.getVenderCode());
				st.setDouble(3, pomain.getTipFee());
				st.setDouble(4, pomain.getProductTotal());
				st.setInt(5, pomain.getPayType());
				st.setString(6, pomain.getRemark());
				st.setDouble(7, pomain.getPrePayFee());
				st.setDouble(8, pomain.getPoTotal());
				st.setLong(9, pomain.getPoId());
				return st.executeUpdate();
			}finally {
				DbUtil.close(null, null, st);
			}
		}
		//根据poId删除
		public int del(String poId) throws SQLException {
			PreparedStatement st = null;
			try {
				st = conn.prepareStatement("delete from pomain where poId=?");
				st.setString(1, poId);
				return st.executeUpdate();
			}finally {
				DbUtil.close(null, null, st);
			}
		}
		public int add(Pomain pomain) throws SQLException {
			PreparedStatement st = null;
			try {
				String sql = "insert into pomain(poId,createTime,venderCode,account,tipFee,productTotal,payType,remark,prePayFee,status,poTotal) values(?,?,?,?,?,?,?,?,?,?,?)";
				st = conn.prepareStatement(sql);
				st.setLong(1, pomain.getPoId());
				st.setString(2, pomain.getCreateTime());
				st.setString(3,pomain.getVenderCode());
				st.setString(4, pomain.getAccount());
				st.setDouble(5, pomain.getTipFee());
				st.setDouble(6, pomain.getProductTotal());
				st.setInt(7, pomain.getPayType());
				st.setString(8,pomain.getRemark());
				st.setDouble(9, pomain.getPrePayFee());
				st.setInt(10, pomain.getStatus());
				st.setDouble(11,pomain.getPoTotal());
				return st.executeUpdate();
			} finally {
				DbUtil.close(null, null, st);
			}
		}
	/**
	 * 采购单月度报表
	 * @param month
	 * @return
	 * @throws SQLException 
	 */
	public Map<String, Object> report(String month) throws SQLException{
		Map<String, Object> map = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = DbUtil.getConn();
			String sql = "select a.*,(ifnull(pay,0)+ifnull(prepay,0)) totalpay from "
					+ "(select count(*) totalnum,count(paytime) paytotalnum,sum(pototal) pototal,SUBSTR(CreateTime,1,7) d from pomain ) a left join "
					+ "(select sum(pototal) pay,SUBSTR(CreateTime,1,7) d from pomain where PayTime is not null ) b on a.d=b.d left join "
					+ "(select sum(PrePayFee) prepay,SUBSTR(CreateTime,1,7) d from pomain where PayTime is null and PrePayTime is not null ) c "
					+ "on b.d = c.d and a.d = ?";
			LOGGER.debug("[pomain_reoprt]-->"+sql);
			ps = conn.prepareStatement(sql);
			ps.setString(1, month);
			rs = ps.executeQuery();
			if(rs.next()){
				map = new HashMap<String, Object>();
				map.put("totalNum", rs.getInt("totalnum"));//采购单总数
				map.put("payTotalNum", rs.getInt("paytotalnum"));//已了结采购单数量
				map.put("pototal", rs.getInt("pototal"));//采购单总金额
				map.put("totalpay", rs.getInt("totalpay"));//已付款总金额
			}
			return map;
		}finally{
			DbUtil.close(conn,rs, ps);
		}
	}
	
	/**
	 *采购单分页显示
	 */
	@Override
	public List<Pomain> selectData(Map<String, String> queryMap) throws SQLException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Pomain> pomains = new ArrayList<Pomain>();
		try{
			//生成动态sql
			StringBuilder sql = new StringBuilder("select * ");
			
			//用来保存查询参数
			Map<Integer, String> params = new HashMap<Integer, String>();
			
			int index = generateQueryCondition(queryMap, params, sql);
			
			//添加分页
			sql.append(" limit ?,?");
			LOGGER.debug("[采购单分页查询]-->"+sql);
			ps = conn.prepareStatement(sql.toString());
			//给?赋值
			for(Integer i:params.keySet()){
				ps.setObject(i, params.get(i));
			}
			//添加分页数据
			ps.setInt(index++, Integer.parseInt(queryMap.get("startNo")));
			ps.setInt(index++, Integer.parseInt(queryMap.get("pageSize")));
			
			rs = ps.executeQuery();
			while (rs.next()) {
				Pomain pomain = new Pomain();
				pomain.setPoId(rs.getLong("PoId"));//采购单编号
				pomain.setCreateTime(rs.getString("createTime"));//创建时间
				pomain.setVenderName(rs.getString("name"));//供应商名字
				pomain.setAccount(rs.getString("account"));//创建用户
				pomain.setTipFee(rs.getDouble("tipfee"));//附加费用
				pomain.setPoTotal(rs.getDouble("pototal"));//采购单总价格
				pomain.setProductTotal(rs.getDouble("producttotal"));//采购单产品总价格
				pomain.setPayType(rs.getInt("payType"));//付款方式
				pomain.setPrePayFee(rs.getDouble("prepayfee"));//最低预付款金额
				pomain.setStatus(rs.getInt("status"));//处理状态
				pomain.setPayTime(rs.getString("paytime"));//操作时间
				pomain.setPayUser(rs.getString("payuser"));//操作用户
				pomain.setPrePayTime(rs.getString("prepaytime"));//操作时间
				pomain.setPrePayUser(rs.getString("prepayuser"));//操作用户
				pomains.add(pomain);
			}
			return pomains;
		}finally{
			DbUtil.close(null,rs, ps);
		}
	}
	
	/**
	 * 生成查询条件
	 */
	@Override
	public int generateQueryCondition(Map<String,String> querymap,final Map<Integer,String> params,final StringBuilder sql) {
		sql.append("from pomain p,vender v where p.vendercode = v.vendercode ");
		int index = 1;
		/*//类型
		String type = querymap.get("type");
		if("-1".equals(type)){//查询采购单主界面
			
		}else if("1".equals(type)){//付款登记主界面
			condition.append(" and (");
			condition.append("(paytype = 1)");
			condition.append(" or ");
			condition.append("(paytype = 2)");
			condition.append(" or ");
			condition.append("(paytype = 3)");
			condition.append(")");
		}else{
			condition.append(" and false");//如果type类型不对 则什么都查不出来
		}*/
		
		String poId = querymap.get("poId");
		if(!StringUtil.isEmpty(poId)){
			sql.append(" and p.poid like concat('%',?,'%')");
			params.put(index++, poId);
		}
		//日期范围（开始日期，截至日期）
		String startDate = querymap.get("startDate");
			if(!StringUtil.isEmpty(startDate)){
				startDate = startDate+" 00:00:00";
				sql.append(" and p.createtime >= ?");
				params.put(index++, startDate);
			}
		String endDate = querymap.get("endDate");
			if(!StringUtil.isEmpty(endDate)){
				endDate = endDate+" 23:59:59";
				sql.append(" and p.createtime <= ?");
				params.put(index++, endDate);
		}
		//供应商选择
		String venderName = querymap.get("venderName");
		if(!StringUtil.isEmpty(venderName)){
			sql.append(" and v.name like concat('%',?,'%')");
			params.put(index++, venderName);
		}
		//付款方式
		String payType = querymap.get("payType");
		if(!StringUtil.isEmpty(payType)){
			sql.append(" and p.payType = ?");
			params.put(index++, payType);
		}
		//处理状态
		String status = querymap.get("status");
		if(!StringUtil.isEmpty(status)){
			sql.append(" and p.status = ?");
			params.put(index++, status);
		}
		return index;
	}
	
	/**
	 * 根据id查看采购单详情
	 * @param poid
	 * @return
	 * @throws SQLException 
	 */
	public Pomain queryByPoid(Long poid) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		Pomain pomain = null;
		
		try{
			String sql = "select m.*,i.*,p.name productName,v.name venderName from pomain m,poitem i,product p,vender v where m.poid = i.poid and p.productCode=i.productCode "
					+ "and v.vendercode = m.vendercode and m.poid=?";
			LOGGER.debug("[采购单详情查询]-->"+sql);
			conn = DbUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, poid);
			rs = ps.executeQuery();
			List<Poitem> poitems = null;
			
			while(rs.next()){
				if(pomain == null){
					pomain = new Pomain();
					poitems = new ArrayList<Poitem>();
					pomain.setPoitems(poitems);
					
					pomain.setPoId(poid);//订单id
					pomain.setCreateTime(rs.getString("createtime"));//创建时间
					pomain.setVenderName(rs.getString("venderName"));//供应商名字
					pomain.setVenderCode(rs.getString("venderCode"));
					pomain.setAccount(rs.getString("account"));//创建用户
					pomain.setTipFee(rs.getDouble("tipfee"));//附加费用
					pomain.setPoTotal(rs.getDouble("pototal"));//采购单总价格
					pomain.setProductTotal(rs.getDouble("producttotal"));//采购单产品总价格
					pomain.setPayType(rs.getInt("paytype"));//付款方式
					pomain.setPrePayFee(rs.getDouble("prepayfee"));//最低预付款金额
					pomain.setRemark(rs.getString("remark"));
					pomain.setEndTime(rs.getString("endTime"));
					pomain.setEndUser(rs.getString("endUser"));
					pomain.setPayTime(rs.getString("payTime"));
					pomain.setPayUser(rs.getString("payUser"));
					pomain.setPrePayTime(rs.getString("prePayTime"));
					pomain.setPrePayUser(rs.getString("prePayUser"));
					pomain.setStockTime(rs.getString("stockTime"));//入库时间
					pomain.setStockUser(rs.getString("stockUser"));//入库记录人
					pomain.setStatus(rs.getInt("status"));
				}
				Poitem item = new Poitem();
				item.setProductCode(rs.getString("productCode"));
				item.setItemPrice(rs.getDouble("itemPrice"));
				item.setNum(rs.getInt("num"));
				item.setProductName(rs.getString("productName"));//产品名称
				item.setUnitName(rs.getString("unitName"));
				item.setUnitPrice(rs.getDouble("unitPrice"));
				poitems.add(item);
			}
			return pomain;
		}finally{
			DbUtil.close(conn,rs, ps);
		}
	}

	public Pomain queryPomainByPoId(Long poid) throws SQLException {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		Pomain pomain = new Pomain();
		String sql = "select * from pomain p , vender v where p.vendercode=v.vendercode and p.poid = ?";
		try {
			conn = DbUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, poid);
			rs = ps.executeQuery();
			while(rs.next()) {
				pomain.setPoId(rs.getLong("poid"));
				pomain.setCreateTime(rs.getString("createtime"));
				pomain.setVenderName(rs.getString("name"));
				pomain.setVenderCode(rs.getString("venderCode"));
				pomain.setAccount(rs.getString("account"));
				pomain.setTipFee(rs.getDouble("tipfee"));
				pomain.setProductTotal(rs.getDouble("producttotal"));
				pomain.setPoTotal(rs.getDouble("poTotal"));
				pomain.setStatus(rs.getInt("status"));
				pomain.setPrePayFee(rs.getDouble("prepayfee"));
				pomain.setRemark(rs.getString("remark"));
				pomain.setPayType(rs.getInt("paytype"));
				pomain.setStockTime(rs.getString("stockTime"));
				pomain.setStockUser(rs.getString("stockUser"));
				pomain.setPayTime(rs.getString("payTime"));
				pomain.setPayUser(rs.getString("payUser"));
				pomain.setPrePayTime(rs.getString("prePayTime"));
				pomain.setPrePayUser(rs.getString("prePayUser"));
				pomain.setEndTime(rs.getString("endTime"));
				pomain.setEndUser(rs.getString("endUser"));
				
			}
			return pomain;
		} finally {
			DbUtil.close(conn,rs, ps);
		}
	}
	
	/**
	 * 付款登记或者是尾款登记
	 * @param account
	 * @param poid
	 * @return
	 * @throws SQLException 
	 */
	public int pay(String account,long poid) throws SQLException{
		Connection conn = null;
		PreparedStatement ps =null;
		try{
			conn = DbUtil.getConn();
			String sql = "update pomain set paytime = now(), payuser = ?, status = ? where poid = ? "
			+"and status = (case paytype when 1 then "+ScmConfig.RECEIVE+" when 2 then "+ScmConfig.ADD+" when 3 then "+ScmConfig.RECEIVE+" end)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, account);
			ps.setInt(2, ScmConfig.PAY);
			ps.setLong(3, poid);
			return ps.executeUpdate();
		}finally{
			DbUtil.close(conn,null, ps);
		}
	}
	/**
	 * 预付款登记
	 * @param account
	 * @param poid
	 * @return
	 * @throws SQLException 
	 */
	public int prePay(String account,long poid) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = DbUtil.getConn();
			String sql = "update poamin set paytime = now(), payuser = ?, status = ? where poid = ? "
			+"and status = (case paytype when 3 then"+ScmConfig.ADD+" end)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, account);
			ps.setInt(2, ScmConfig.PREPAY);
			ps.setLong(3, poid);
			return ps.executeUpdate();
		}finally{
			DbUtil.close(conn,null, ps);
		}
	}
	
}
